package com.mini.controller;

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.mini.conditon.BusinessConditionNew;
import com.mini.config.CheckLogin;
import com.mini.config.UnLoginException;
import com.mini.constant.RedisKeyConst;
import com.mini.controller.BaseController;
import com.mini.model.BusinessPlanSitePageR;
import com.mini.res.BusinessPlanRES;
import com.mini.res.BusinessPlanSitePageRRES;
import com.mini.service.BusinessPlanService;
import com.mini.service.MiniAdminService;
import com.mini.util.CalendarUtil;
import com.mini.util.UploadAliyunUtil;
import com.mini.vo.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.concurrent.TimeUnit;

import static com.mini.util.CalendarUtil.SIMPLE_DATE_FORMAT_NO_DASH;

/**
 * @description: 小课计划
 */
@RestController
@RequestMapping("/plan")
@Api(tags = "计划相关接口")
@Slf4j
public class BusinessPlanController extends BaseController {

    @Autowired
    private BusinessPlanService planService;

    @Autowired
    private MiniAdminService miniAdminService;

    @Autowired
    private UploadAliyunUtil uploadAliyunUtil;

    @Value("${test.plansite}")
    private Long plansite;

    @ApiOperation(value = "新增计划", notes = "新增计划")
    @PostMapping("/add")
    @CheckLogin
    public BusinessResult addPlan(@ApiParam(value = "token", required = true) @RequestHeader String Authorization,
                                  @RequestBody @ApiParam(required = true, value = "计划VO") BusinessPlanVO businessPlanVO){
        try{
            if ((StringUtils.isBlank(businessPlanVO.getPageSite())) || StringUtils.isBlank(businessPlanVO.getPlanName()) ||
                null == businessPlanVO.getFzId() || null == businessPlanVO.getLessonSite() ) {
                log.info("参数错误 ={}", businessPlanVO);
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR);
            }
            UserVO loginUser = getLoginUser(Authorization);
            String baseKey = RedisKeyConst.getBusinessNewFlag( "plan", loginUser.getId());
            redisTemplate.delete(baseKey);
            BusinessPlanSitePageR businessPlanSitePageR = planService.add(businessPlanVO);
            redisTemplate.opsForSet().add(baseKey, businessPlanSitePageR.getPlanSite());
            redisTemplate.expire(baseKey, 30 , TimeUnit.DAYS);
            return BusinessResult.createSuccessInstance("ok");
        }catch (Exception ex){
            log.error("新增计划 error ex  ",ex);
            return BusinessResult.createInstance(CommonResultMessage.ERROR, ex.getMessage());
        }
    }


    @ApiOperation(value = "批量新增计划", notes = "批量新增计划")
    @PostMapping("/batch/add")
    @CheckLogin
    public BusinessResult batchAddPlan(@ApiParam(value = "token", required = true) @RequestHeader String Authorization,
                                       @RequestBody @ApiParam(required = true, value = "计划VO List") BusinessPlanVOList businessPlanVOList){
        try{
            List<BusinessPlanVO> businessPlanVOLists = businessPlanVOList.getBusinessPlanVOList();
            if (null == businessPlanVOList || businessPlanVOLists.size() == 0) {
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR, "实体为空");
            }
            UserVO loginUser = getLoginUser(Authorization);
            if (planService.batchAddPlan(loginUser.getId(), businessPlanVOLists))
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR);
            return BusinessResult.createSuccessInstance("ok");
        }catch (Exception ex){
            log.error("新增计划 error ex  ",ex);
            return BusinessResult.createInstance(CommonResultMessage.ERROR, ex.getMessage());
        }
    }



    @ApiOperation(value = "更新计划", notes = "更新计划")
    @PostMapping("/edit")
    @CheckLogin
    public BusinessResult<String> updatePlan(@ApiParam(value = "token", required = true) @RequestHeader String Authorization,
                                                               @RequestBody @ApiParam(required = true, value = "计划VO") BusinessPlanVO businessPlanVO){
        try{
            if (null == businessPlanVO.getNextDayLessonSite() || null == businessPlanVO.getPlanSite()) {
                log.info("参数错误 ={}", businessPlanVO);
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR);
            }
            log.info("businessPlanVO={}", businessPlanVO);
            planService.update(businessPlanVO.getPlanSite(), businessPlanVO.getNextDayLessonSite(), businessPlanVO.getFzId());
            return BusinessResult.createSuccessInstance("ok");
        }catch (Exception ex){
            log.error("更新计划 error ex",ex);
            return BusinessResult.createInstance(CommonResultMessage.ERROR, ex.getMessage());
        }
    }

    @ApiOperation(value = "批量更新计划", notes = "批量更新计划")
    @PostMapping("/batch/edit")
    @CheckLogin
    public BusinessResult<String> batchUpdatePlan(@ApiParam(value = "token", required = true) @RequestHeader String Authorization,
                                                                    @RequestBody @ApiParam(required = true, value = "批量更新计划VO") BusinessPlanVOList businessPlanVOList){
        try{
            List<BusinessPlanVO> businessPlanVOLists = businessPlanVOList.getBusinessPlanVOList();
            if (null == businessPlanVOList || businessPlanVOLists.size() == 0) {
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR, "实体为空");
            }
            if (planService.batchUpdate(businessPlanVOLists))
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR);
            return BusinessResult.createSuccessInstance("ok");
        }catch (Exception ex){
            log.error("更新计划 error ex",ex);
            return BusinessResult.createInstance(CommonResultMessage.ERROR, ex.getMessage());
        }
    }

    @CheckLogin
    @ApiOperation(value = "获取我的计划列表，分页", notes = "获取我的计划列表，分页")
    @PostMapping("/page")
    public BusinessResult<PageResultVO<BusinessPlanSitePageRRES>> page(@ApiParam(value = "token", required = true) @RequestHeader String Authorization,
                                                                       @ApiParam(value = "查询条件", required = true) @RequestBody BusinessConditionNew businessCondition){
        checkPageParams(businessCondition);
        Long loginUserId = this.miniAdminService.getLoginUserId(Authorization);
        if(loginUserId == null){
            throw new UnLoginException("未登录！");
        }else {
            businessCondition.setCreateUserId(loginUserId);
        }
        businessCondition.setSortColumn("updateTime");
        log.info("businessCondition={}", businessCondition);
        PageResultVO<BusinessPlanSitePageRRES> page = planService.page(businessCondition, loginUserId, false);
        return BusinessResult.createSuccessInstance(page);
    }


    @CheckLogin
    @ApiOperation(value = "导出我的计划列表", notes = "导出我的计划列表")
    @PostMapping("/export/list")
    public BusinessResult<PageResultVO<BusinessPlanSitePageRRES>> export(@ApiParam(value = "token", required = true) @RequestHeader String Authorization,
                                                                         @ApiParam(value = "查询条件", required = true) @RequestBody BusinessConditionNew businessCondition){
        Long loginUserId = this.miniAdminService.getLoginUserId(Authorization);
        if(loginUserId == null){
            throw new UnLoginException("未登录！");
        }else {
            businessCondition.setCreateUserId(loginUserId);
        }
        businessCondition.setSortColumn("updateTime");
        businessCondition.setPageSize(500);
        businessCondition.setPageNumber(1);
        log.info("businessCondition={}", businessCondition);
        PageResultVO<BusinessPlanSitePageRRES> page = planService.page(businessCondition, loginUserId, true);
        log.debug("导出page={}", page);
        List<BusinessPlanSitePageRRES> rows = page.getRows();
        String path = "/tmp";
        File f = new File(path);
        if (!f.exists()) {
            f.mkdir();
        }
        String fileName = "计划SXSSF" + CalendarUtil.getDateString(new Date(), SIMPLE_DATE_FORMAT_NO_DASH) +".xlsx";
        log.info("fileName={}", fileName);
        File file = new File(f, fileName);
        if (!file.exists()) {
            try {
                file.createNewFile();
            } catch (IOException e) {
                log.error("创建文件异常", e);
                return BusinessResult.createInstance(CommonResultMessage.ERROR);
            }
        }
        long start = System.currentTimeMillis();
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(6000);
        FileOutputStream fos = null;
        List downPathList = new ArrayList();
        try {
            Sheet sheet = sxssfWorkbook.createSheet("第一页");
            sheet.createFreezePane(1,1);
            XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
            XSSFCellStyle xssfCellStyleOne = getAndSetXSSFCellStyleOne(sxssfWorkbook);
            XSSFCellStyle xssfCellStyleTwo = getAndSetXSSFCellStyleTwo(sxssfWorkbook);
            // 创建第一行,作为header表头
            Row header = sheet.createRow(0);
            int colSum = 12;
            // 循环创建header单元格(根据实际情况灵活创建即可)
            for (int cellnum = 0; cellnum < colSum; cellnum++) {
                Cell cell = header.createCell(cellnum);
                cell.setCellStyle(xssfCellStyleHeader);
                // 判断单元格
                if (cellnum == 0) {
                    cell.setCellValue("计划站点");
                } else if (cellnum == 1) {
                    cell.setCellValue("计划名称");
                } else if (cellnum == 2) {
                    cell.setCellValue("计划状态");
                } else if (cellnum == 3) {
                    cell.setCellValue("投放渠道");
                } else if (cellnum == 4) {
                    cell.setCellValue("页面站点");
                } else if (cellnum == 5) {
                    cell.setCellValue("业务类型");
                } else if (cellnum == 6) {
                    cell.setCellValue("小课站点");
                } else if (cellnum == 7) {
                    cell.setCellValue("次日小课站点");
                } else if (cellnum == 8) {
                    cell.setCellValue("量子军团");
                } else if (cellnum == 9) {
                    cell.setCellValue("负责人");
                } else if (cellnum == 10) {
                    cell.setCellValue("军团");
                } else if (cellnum == 11) {
                    cell.setCellValue("落地页链接");
                }
            }
            int size = rows.size();
            // 遍历创建行,导出数据
            for (int rownum = 1; rownum <= size; rownum++) {
                Row row = sheet.createRow(rownum);
                // 循环创建单元格
                for (int cellnum = 0; cellnum < colSum; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    // 根据行数,设置该行内的单元格样式
                    if (rownum % 2 == 1) { // 奇数
                        cell.setCellStyle(xssfCellStyleOne);
                    } else { // 偶数
                        cell.setCellStyle(xssfCellStyleTwo);
                    }
                    // 根据单元格所属,录入相应内容
                    // 将部分数字类型的字符串,转换为Long;以免导出excel后,单元格左上角有三
                    //    角形(这是excel检查到该单元格内的内容均为数字,但是单元格类型却不是
                    //    数字,给出的提示),转不转看自己需求灵活处理
                    BusinessPlanSitePageRRES businessPlanSitePageRRES = rows.get(rownum - 1);
                    if (cellnum == 0) {
                        cell.setCellValue((businessPlanSitePageRRES.getPlanSite()));
                    } else if (cellnum == 1) {
                        cell.setCellValue(businessPlanSitePageRRES.getPlanName());
                    } else if (cellnum == 2) {
                        Integer status = businessPlanSitePageRRES.getStatus();
                        if (status == -2 || status == -3) {
                            cell.setCellValue("未绑定计划");
                        } else if (status == 1) {
                            cell.setCellValue("启用中");
                        } else if (status == 0) {
                            cell.setCellValue("暂停中");
                        }
                    } else if (cellnum == 3) {
                        cell.setCellValue(businessPlanSitePageRRES.getChannelName());
                    } else if (cellnum == 4) {
                        cell.setCellValue(businessPlanSitePageRRES.getPageSite());
                    } else if (cellnum == 5) {
                        cell.setCellValue(businessPlanSitePageRRES.getSiteTypeName());
                    }
                    else if (cellnum == 6) {
                        cell.setCellValue(businessPlanSitePageRRES.getLessonSite());
                    } else if (cellnum == 7) {
                        Long nextDayLessonSite = businessPlanSitePageRRES.getNextDayLessonSite();
                        if (nextDayLessonSite == null || nextDayLessonSite == 0) {
                            cell.setCellValue("无");
                        } else {
                            cell.setCellValue(nextDayLessonSite);
                        }
                    } else if (cellnum == 8) {
                        cell.setCellValue(businessPlanSitePageRRES.getGroupNames());
                    } else if (cellnum == 9) {
                        cell.setCellValue(businessPlanSitePageRRES.getFzName());
                    } else if (cellnum == 10) {
                        cell.setCellValue(businessPlanSitePageRRES.getWorkRoomName());
                    } else if (cellnum == 11) {
                        cell.setCellValue(businessPlanSitePageRRES.getTouFangUrl());
                    }
                }
            }

            // 在后面设置sheet
            setSheet(sheet);
            fos = new FileOutputStream(file);
            sxssfWorkbook.write(fos);
            long end = System.currentTimeMillis();
            log.info("数据全部导出至excel总耗时:{} 毫秒!", end - start, size);
            String e = uploadAliyunUtil.uploadOssFile1(file, "gyf", "excel");
            log.info("e={}", e);
            downPathList.add(e);
        } catch (Throwable e) {
            log.error( "转换成excel失败 e={}", e);
        } finally {
            try {
                if(sxssfWorkbook != null) {
                    sxssfWorkbook.dispose();
                }
                if(fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        log.info("downPathList={}", downPathList);
        return BusinessResult.createSuccessInstance(downPathList);
    }

    @CheckLogin
    @ApiOperation(value = "根据planSite 查询信息", notes = "根据planSite 查询信息")
    @GetMapping("/relationinfo")
    public BusinessResult<BusinessPlanRES> getPlanSiteRelationInfo(@ApiParam(value = "token", required = true) @RequestHeader String Authorization,
                                                                   @ApiParam(value = "计划站点", required = true) @RequestParam Long planSite){
        BusinessPlanRES planRES = planService.getPlanSiteRelationInfo(planSite);
        return BusinessResult.createSuccessInstance(planRES);
    }

    //    @CheckLogin
    @ApiOperation(value = "根据lessonSite集合 查询计划名称信息", notes = "根据lessonSite集合 查询计划名称信息")
    @PostMapping("/plan/lesson")
    public BusinessResult<HashMap<Long, List<String>>> getPlanNamesByLessonSites(
//            @ApiParam(value = "token", required = true) @RequestHeader String Authorization,
            @ApiParam(value = "计划站点", required = true)    @RequestBody List<Long>  lessonSites){
//        List<Long> lessonSiteList = Splitter.on(",").omitEmptyStrings().splitToList(lessonSites).stream().map(s -> Long.parseLong(s.trim())).collect(Collectors.toList());
        log.debug("lessonSiteList={}", lessonSites);
        return BusinessResult.createSuccessInstance(planService.listPlanName(lessonSites));
    }


//    @CheckLogin
//    @ApiOperation(value = "根据计划id查询接待量数据", notes = "根据计划id查询接待量数据")
//    @GetMapping("/creceptions")
//    public BusinessResult<List<MiniStatisticPreceptionVO>> getPlanCreception(
//            @ApiParam(value = "token", required = true) @RequestHeader String Authorization,
//            @ApiParam(value = "计划站点", required = true) @RequestParam Long planSite){
//        return BusinessResult.createSuccessInstance(planService.getPlanCreceptions(planSite));
//    }

    //批量上传
    @CheckLogin
    @ApiOperation(value = "新建个人上传Excel模版")
    @PostMapping(value = "/_self/_upload", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public BusinessResult uploadadd(@ApiParam(value = "Authorization", required = true) @RequestHeader String Authorization,
                                    @RequestParam("file") MultipartFile file) throws Exception {
        try {
            String originalFilename = file.getOriginalFilename();
            InputStream inputStream = file.getInputStream();
            Workbook workbook = getWorkbook(inputStream, originalFilename);
            if (null == workbook) {
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR, "excel为空");
            }
            Sheet sheet = workbook.getSheetAt(0);
            if (null == sheet) {
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR, "excel sheet为空");
            }
            int lastRowNum = sheet.getLastRowNum();
            List<JSONObject> arrayList = Lists.newArrayList();
            for (int i = 1; i <= lastRowNum; i++) {
                try {
                    JSONObject jsonObject = new JSONObject();
                    Row row = sheet.getRow(i);
                    try {
                        String cellValue = row.getCell(0).getStringCellValue();
                        jsonObject.put("planSiteName", cellValue);
                    } catch (Exception e) {
                        log.error("planSiteName 异常",e.getMessage());
                    }
                    Double numericCellValue = null;
                    try {
                        numericCellValue = row.getCell(1).getNumericCellValue();
                        jsonObject.put("miniLessionSite", numericCellValue.intValue());
                    } catch (Exception e) {
                        log.error("miniLessionSite 异常", e.getMessage());
                    }
                    try {
                        numericCellValue = row.getCell(2).getNumericCellValue();
                        jsonObject.put("pageSite", numericCellValue.intValue());
                    } catch (Exception e) {
                        log.error("pageSite 异常", e.getMessage());
                    }
                    arrayList.add(jsonObject);
                } catch (Exception e) {
                    log.error("个人上传Excel模版异常", e.getMessage());
                }
            }
            return BusinessResult.createSuccessInstance(arrayList);
        } catch (Exception e) {
            log.error("新建个人上传Excel模版异常", e);
            return BusinessResult.createInstance(CommonResultMessage.ERROR, e.getMessage());
        }
    }


    //批量上传
    @CheckLogin
    @ApiOperation(value = "批量切个人上传Excel模版")
    @PostMapping(value = "/update/_upload", produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
    public BusinessResult uploadReply(@ApiParam(value = "Authorization", required = true) @RequestHeader String Authorization,
                                      @RequestParam("file") MultipartFile file) throws Exception {
        try {
            String originalFilename = file.getOriginalFilename();
            InputStream inputStream = file.getInputStream();
            Workbook workbook = getWorkbook(inputStream, originalFilename);
            if (null == workbook) {
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR, "excel为空");
            }
            Sheet sheet = workbook.getSheetAt(0);
            if (null == sheet) {
                return BusinessResult.createInstance(CommonResultMessage.PARAMS_ERROR, "excel sheet为空");
            }
            int lastRowNum = sheet.getLastRowNum();
            List<JSONObject> arrayList = Lists.newArrayList();
            for (int i = 1; i <= lastRowNum; i++) {
                try {
                    JSONObject jsonObject = new JSONObject();
                    Row row = sheet.getRow(i);
                    Double numericCellValue = null;
                    try {
                        numericCellValue = row.getCell(0).getNumericCellValue();
                        jsonObject.put("planSite", numericCellValue.intValue());
                    } catch (Exception e) {
                        log.error("planSite异常={}", e.getMessage());
                    }
                    try {
                        numericCellValue = row.getCell(1).getNumericCellValue();
                        jsonObject.put("nextDayLessonSite", numericCellValue.intValue());
                    } catch (Exception e) {
                        log.error("nextDayLessonSite异常={}", e.getMessage());
                    }
                    arrayList.add(jsonObject);
                } catch (Exception e) {
                    log.error("个人上传Excel模版异常", e.getMessage());
                }
            }
            return BusinessResult.createSuccessInstance(arrayList);
        } catch (Exception e) {
            log.error("个人上传Excel模版异常", e);
            return BusinessResult.createInstance(CommonResultMessage.ERROR, e.getMessage());
        }
    }

    /**
     * 获取并设置header样式
     */
    private XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        Font font = sxssfWorkbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 14);
        // 字体粗细
        font.setBoldweight((short) 20);
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return xssfCellStyle;
    }

    /**
     * 获取并设置样式一
     */
    private XSSFCellStyle getAndSetXSSFCellStyleOne(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        XSSFDataFormat format = (XSSFDataFormat)sxssfWorkbook.createDataFormat();
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 前景颜色
//        xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//        xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        // 边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
        xssfCellStyle.setDataFormat(format.getFormat("0"));
        return xssfCellStyle;
    }

    /**
     * 获取并设置样式二
     */
    private XSSFCellStyle getAndSetXSSFCellStyleTwo(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        XSSFDataFormat format = (XSSFDataFormat)sxssfWorkbook.createDataFormat();
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
        xssfCellStyle.setDataFormat(format.getFormat("0"));
        return xssfCellStyle;
    }

    /**
     * 设置sheet
     */
    private void setSheet(Sheet sheet) {
        // 设置各列宽度(单位为:字符宽度的1/256)
        sheet.setColumnWidth(0, 32 * 256);
        sheet.setColumnWidth(1, 32 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 60 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        sheet.setColumnWidth(6, 20 * 256);
        sheet.setColumnWidth(7, 20 * 256);
        sheet.setColumnWidth(8, 20 * 256);
        sheet.setColumnWidth(9, 20 * 256);
        sheet.setColumnWidth(10, 20 * 256);
    }

}
